﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;
using Utils;

public static class MySqlDb
{
    public static string strSql = "Server=101.37.175.132;Database=chat;Uid=zhangdashuai;Pwd=ow9eOhg6Z4IYe$Fm;";
    /// <summary>
    /// 执行一个没有返回值的方法
    /// </summary>
    /// <param name="strSql">Sql语句</param>
    /// <param name="parameters">参数化语句</param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string strSql, MySqlParameter[] parameters = null)
    {
        using (var conn = new MySqlConnection(MySqlDb.strSql))
        {
            conn.Open();
            using (var cmd = new MySqlCommand(strSql, conn))
            {
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                return cmd.ExecuteNonQuery();
            }
        }
    }

    public static DataTable GetDataTable(string strSql, MySqlParameter[] parameters = null)
    {
        using (var conn = new MySqlConnection(MySqlDb.strSql))
        {
            conn.Open();
            using (var cmd = new MySqlCommand(strSql, conn))
            {
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                System.Data.DataTable dataTable = new System.Data.DataTable();
                var reader = cmd.ExecuteReader();
                var column = reader.FieldCount;
                for (int i = 0; i < column; i++)
                {
                    //  循环出来datatable
                    DataColumn newColumn = new DataColumn();
                    newColumn.ColumnName = reader.GetName(i);
                    var e = reader.GetName(i).GetType();
                    dataTable.Columns.Add(newColumn);
                }
                if (reader.HasRows)
                {

                    while (reader.Read())
                    {
                        var str = reader[0];
                        DataRow dr = dataTable.NewRow();
                        for (int i = 0; i < column; i++)
                        {
                            var f = reader[i].GetType();
                            var columnName = reader.GetName(i);
                            //  循环出来datatable
                            //dr[i] = System.Type.GetType("System.Int32")reader[i];
                            //dr[i] =DBNull( reader[i]);

                            dr[i] = reader[i];

                        }
                        dataTable.Rows.Add(dr);
                        //dataTable.Rows.Add

                    }
                }
                return dataTable;
                //return cmd.ExecuteNonQuery();
            }
        }
    }

    private static object DBNull(object v)
    {
        throw new NotImplementedException();
    }

    /// <summary>
    /// 执行一个事务方法
    /// </summary>
    /// <param name="strSql">Sql语句</param>
    /// <param name="parameters">参数化语句</param>
    /// <returns></returns>
    public static bool ExecuteNonQuery(string[] strSql, List<MySqlParameter[]> parameters)
    {
        using (var conn = new MySqlConnection(MySqlDb.strSql))
        {
            conn.Open();
            var tran = conn.BeginTransaction();
            using (var cmd = new MySqlCommand())
            {
                cmd.Transaction = tran;
                cmd.Connection = conn;
                try
                {
                    for (int i = 0; i < strSql.Length; i++)
                    {
                        cmd.Parameters.Clear();
                        cmd.CommandText = strSql[i];
                        cmd.Parameters.AddRange(parameters[i]);
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    return true;
                }
                catch
                {
                    tran.Rollback();
                    tran.Dispose();
                    return false;
                }
            }
        }
    }

    /// <summary>
    /// 执行单个有返回值的方法
    /// </summary>
    /// <param name="strSql">Sql语句</param>
    /// <param name="parameters">参数化语句</param>
    /// <returns></returns>
    public static object GetSingleData(string strSql, MySqlParameter[] parameters)
    {
        using (var conn = new MySqlConnection(MySqlDb.strSql))
        {
            conn.Open();
            using (var cmd = new MySqlCommand(strSql, conn))
            {
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteScalar();
            }
        }
    }

    /// <summary>
    /// 获取MODEL
    /// </summary>
    /// <param name="soucre">T</param>
    /// <param name="strSql">SQl语句</param>
    /// <param name="parameters">参数化</param>
    /// <returns></returns>
    public static T SqlGetModel<T>(this T soucre, string strSql, MySqlParameter[] parameters = null)
    {
        using (var conn = new MySqlConnection(MySqlDb.strSql))
        {
            conn.Open();
            using (var cmd = new MySqlCommand(strSql, conn))
            {
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                var read = cmd.ExecuteReader();
                Type type = typeof(T);
                PropertyInfo[] pArray = type.GetProperties(); //集合属性数组 
                T entity = Activator.CreateInstance<T>();
                if (read.HasRows)
                {
                    while (read.Read())
                    {
                        foreach (PropertyInfo p in pArray)
                        {
                            try
                            {
                                if (read[p.Name] == null)
                                {
                                    continue;  //DataTable列中不存在集合属性或者字段内容为空则，跳出循环，进行下个循环     
                                }
                                var obj = Convert.ChangeType(read[p.Name], p.PropertyType);//类型强转，将table字段类型转为集合字段类型    
                                p.SetValue(entity, obj, null);
                            }
                            catch
                            {
                            }
                        }
                    }
                    return entity;
                }
                return default(T);
            }
        }
    }


    /// <summary>
    /// 获取List
    /// </summary>
    /// <param name="soucre">T</param>
    /// <param name="strSql">SQl语句</param>
    /// <param name="parameters">参数化</param>
    /// <returns></returns>
    public static List<T> GetList<T>(this IEnumerable<T> soucre, string strSql, MySqlParameter[] parameters = null)
    {
        using (var conn = new MySqlConnection(MySqlDb.strSql))
        {
            conn.Open();
            using (var cmd = new MySqlCommand(strSql, conn))
            {
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                var read = cmd.ExecuteReader();
                List<T> list = new List<T>();
                Type type = typeof(T);
                PropertyInfo[] pArray = type.GetProperties(); //集合属性数组 
                if (read.HasRows)
                {
                    while (read.Read())
                    {
                        T entity = Activator.CreateInstance<T>();
                        foreach (PropertyInfo p in pArray)
                        {
                            try
                            {
                                if (read[p.Name] == null)
                                {
                                    continue;  //DataTable列中不存在集合属性或者字段内容为空则，跳出循环，进行下个循环     
                                }
                                var obj = Convert.ChangeType(read[p.Name], p.PropertyType);//类型强转，将table字段类型转为集合字段类型    
                                p.SetValue(entity, obj, null);
                            }
                            catch
                            {
                            }
                        }
                        list.Add(entity);
                    }
                    return list;
                }
                return new List<T>();
            }
        }
    }

    /// <summary>
    /// 获取插入T-Sql语句
    /// </summary>
    /// <param name="soucer"></param>
    /// <param name="parameters">返回的参数</param>
    /// <returns></returns>
    public static string GetInsertSql<T>(this T soucer, out MySqlParameter[] parameters)
    {
        string strSql = "insert into " + soucer.GetType().Name + "(";
        string parameter = "", field = "";
        PropertyInfo[] pi = soucer.GetType().GetProperties();
        parameters = new MySqlParameter[pi.Length];
        for (int i = 0; i < pi.Length; i++)
        {
            object data = pi[i].GetValue(soucer, null);
            if (data != null)
            {
                parameters[i] = new MySqlParameter(pi[i].Name, data);
                field += pi[i].Name + ",";
                if (data.GetType().Name == "DateTime")
                {
                    parameter += "'" + ((DateTime)data).ToString("yyyy-MM-dd HH:mm:ss") + "',";
                }
                else
                {
                    parameter += "'" + data + "',";
                }
            }
        }
        parameter = parameter.Remove(parameter.Length - 1);
        field = field.Remove(field.Length - 1);
        strSql += field + ") values(" + parameter + ")";

        return strSql;
    }


    /// <summary>
    /// 插入数据库
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="soucer"></param>
    /// <returns></returns>
    public static int ModelInsertToSql<T>(this T soucer)
    {
        string strSql = soucer.GetInsertSql(out MySqlParameter[] param);
        return MySqlDb.ExecuteNonQuery(strSql, param);
    }

    /// <summary>
    /// 快速遍历
    /// </summary>
    /// <param name="dataTable"></param>
    /// <param name="func"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static List<T> ErgodicDataTable<T>(DataTable dataTable, Func<DataRow, T> func)
    {
        List<T> list = new List<T>();
        for (int i = 0; i < dataTable.Rows.Count; i++)
        {
            var row = dataTable.Rows[i];
            list.Add(func(row));
        }
        return list;
    }

    /// <summary>
    /// 通过datatable 字段自动判断数据类型
    /// </summary>
    /// <param name="data"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static T ChangeTableData<T>(object data)
    {
        if (data == System.DBNull.Value)
        {
            return default(T);
        }
        else
        {
            var typeCode = System.Type.GetTypeCode(typeof(T));
            return (T)Convert.ChangeType(data, typeCode);
        }
    }

}